package com.crm.crm.mapper;

import com.crm.entity.Partner;
import com.crm.model.Telephone;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface CrmPartnerMapper {

    /**
     * 添加用户信息
     *
     * @param partner
     * @return
     */
    @Insert("INSERT INTO crm_partner\n" +
            "(partner_id, name, sex, telephone, weixin, email, company, department, post," +
            " source, item, debt, address, disabled, create_user_id, create_time, " +
            "update_time, over_area, partner_type, org_type,org_remark,asset_info, fund_info, exit_info, justice_info, peer_info, org_info,note,telephone_list,exit_type)\n" +
            "VALUES(#{partnerId},#{name},#{sex},#{telephone},#{weixin},#{email},#{company},#{department},#{post},#{source},#{item},#{debt},#{address},0,#{createUserId}," +
            "#{createTime},#{time}," +
            "#{overArea,typeHandler=com.crm.typehandler.JsonTypeHandler}," +
            "#{partnerType},#{orgType,typeHandler=com.crm.typehandler.JsonTypeHandler},#{orgRemark}" +
            ",#{assetInfo,typeHandler=com.crm.typehandler.JsonTypeHandler}" +
            ",#{fundInfo,typeHandler=com.crm.typehandler.JsonTypeHandler}" +
            ",#{exitInfo,typeHandler=com.crm.typehandler.JsonTypeHandler}" +
            ",#{justiceInfo,typeHandler=com.crm.typehandler.JsonTypeHandler}" +
            ",#{peerInfo,typeHandler=com.crm.typehandler.JsonTypeHandler}" +
            ",#{orgInfo,typeHandler=com.crm.typehandler.JsonTypeHandler},#{note},#{telephoneList,typeHandler=com.crm.typehandler.JsonTypeHandler},#{exitType})")
    int insert(Partner partner);

    /**
     * 根据id更新数据
     *
     * @param partner
     * @return
     */
    @Update("UPDATE crm_partner\n" +
            "SET name=#{name}, sex=#{sex},telephone=#{telephone}, telephone_list=#{telephoneList,typeHandler=com.crm.typehandler.JsonTypeHandler}, weixin=#{weixin}, email=#{email}, company=#{company}," +
            " department=#{department}, post=#{post}, source=#{source}," +
            " item=#{item}, debt=#{debt}, address=#{address}, disabled=0, create_user_id=#{createUserId}," +
            " create_time=#{createTime}, update_time=#{time}," +
            " over_area=#{overArea,typeHandler=com.crm.typehandler.JsonTypeHandler}, " +
            "partner_type=#{partnerType}," +
            "org_type=#{orgType,typeHandler=com.crm.typehandler.JsonTypeHandler}," +
            "org_remark=#{orgRemark}, " +
            "asset_info=#{assetInfo,typeHandler=com.crm.typehandler.JsonTypeHandler}" +
            ",fund_info=#{fundInfo,typeHandler=com.crm.typehandler.JsonTypeHandler}" +
            ",exit_info=#{exitInfo,typeHandler=com.crm.typehandler.JsonTypeHandler}" +
            ",justice_info=#{justiceInfo,typeHandler=com.crm.typehandler.JsonTypeHandler}" +
            ",peer_info=#{peerInfo,typeHandler=com.crm.typehandler.JsonTypeHandler}" +
            ",org_info=#{orgInfo,typeHandler=com.crm.typehandler.JsonTypeHandler},note=#{note},exit_type=#{exitType} " +
            " WHERE partner_id=#{partnerId}")
    int update(Partner partner);


    /**
     * 根据id，和类型查询
     */

    @Select("select * from crm_partner where partner_id=#{partnerId} and disabled=0")
    @Results(id = "partnerMap", value = {
            @Result(column = "org_type", property = "orgType", typeHandler = com.crm.typehandler.JsonTypeHandler.class),
            @Result(column = "telephone_list", property = "telephoneList", typeHandler = com.crm.typehandler.JsonTypeHandler.class),
            @Result(column = "over_area", property = "overArea", typeHandler = com.crm.typehandler.JsonTypeHandler.class),
            @Result(column = "asset_info", property = "assetInfo", typeHandler = com.crm.typehandler.JsonTypeHandler.class),
            @Result(column = "fund_info", property = "fundInfo", typeHandler = com.crm.typehandler.JsonTypeHandler.class),
            @Result(column = "exit_info", property = "exitInfo", typeHandler = com.crm.typehandler.JsonTypeHandler.class),
            @Result(column = "justice_info", property = "justiceInfo", typeHandler = com.crm.typehandler.JsonTypeHandler.class),
            @Result(column = "peer_info", property = "peerInfo", typeHandler = com.crm.typehandler.JsonTypeHandler.class),
            @Result(column = "org_info", property = "orgInfo", typeHandler = com.crm.typehandler.JsonTypeHandler.class),
    })
    Partner get(String partnerId);

    /**
     * 根据用户id删除信息
     *
     * @param partnerId
     * @return
     */
    @Delete("UPDATE crm_partner SET disabled=1 WHERE partner_id=#{partnerId}")
    int remove(String partnerId);


    /**
     * 查询所有数据
     */
    @ResultMap(value = "partnerMap")
    @Select("SELECT * from crm_partner where disabled=0")
    List<Partner> findAll();

    /**
     * 根据partType类型查询所有
     */
    @ResultMap(value = "partnerMap")
    @Select("select * from crm_partner where partner_type=#{partnerType} " +
            "and disabled=0 and create_user_id=#{createId} order BY update_time  DESC")
    List<Partner> findByPartType(Integer partnerType, String createId);

    /**
     * 根据用户id 类型查询数据
     *
     * @param createUserId
     * @param partnerType
     * @return
     */
    @ResultMap(value = "partnerMap")
    @Select("select * from crm_partner where create_user_id=#{createUserId} and partner_type=#{partnerType} and disabled=0 order BY update_time  DESC")
    List<Partner> findByCreatUser(@Param("createUserId") String createUserId, @Param("partnerType") String partnerType);

    /**
     * 根据类型、城市查询
     *
     * @param partnerType
     * @param cities
     * @return
     */

    @Select({
            "<script>",
            "select * from crm_partner ",
            "where disabled=0 and partner_type=#{partnerType} and ",
            "<foreach collection='cities' item='id' open='(' separator=' or ' close=')'>",
            " JSON_CONTAINS(over_area->'$[*][1]','\"${id}\"')",
            "</foreach>" +
                    " order BY update_time  DESC",
            "</script>",
    })
    @ResultMap(value = "partnerMap")
    List<Partner> findByCity(@Param("partnerType") Integer partnerType,
                             @Param("cities") String[] cities);

    /**
     * 根据手机、微信、邮箱统计个数
     *
     * @return
     */
    @Select("select count(*) from crm_partner" +
            " where disabled=0 and create_user_id=#{create_user_id}" +
            " and partner_type=#{partner_type} and telephone=#{telephones}")
    int findByOnly(String create_user_id, String telephones, Integer partner_type);

    @Select("select count(*) from crm_partner where disabled=0 and create_user_id=#{create_user_id} and partner_type=#{partner_type} and weixin=#{weixin}")
    int findWeixin(String create_user_id, String weixin, Integer partner_type);

    @Select("select count(*) from crm_partner where disabled=0 and create_user_id=#{create_user_id} and partner_type=#{partner_type} and email=#{email} and name=#{name}")
    int findEmail(String create_user_id, String email, Integer partner_type, String name);


    //根据 asset_info信息查询
    @ResultMap(value = "partnerMap")
    @Select({
            "<script>",
            "select crm_partner.*,sys_user.true_name as trueName from crm_partner left join sys_user on crm_partner.create_user_id =sys_user.user_id" +
                    " where   crm_partner.partner_type = #{partnerType} and crm_partner.disabled=0 " +
                    "<if test='company != null and company!=\"\"'>" +
                    " and company like '%${company}%' ",
            "</if>" +
                    "<if test='createId != null and createId!=\"\"'>" +
                    " and crm_partner.create_user_id = #{createId}",
            "</if>" +
                    "<if test='typeId != null and typeId!=\"\"'>" +
                    " and json_contains(asset_info->'$.businessType[*]','\"${typeId}\"')",
            "</if>" +
                    "<if test='belong != null and belong!=\"\"'>" +
                    " and asset_info->'$.belong'=#{belong}",
            "</if>",
            "<if test='orgType != null and orgType!=\"\"'>" +
                    "  and  JSON_CONTAINS(org_type->'$[*]' , '\"${orgType}\"')",
            "</if>" +
                    "<if test='orgRemark != null and orgRemark!=\"\"'>" +
                    " and crm_partner.org_remark = #{orgRemark}",
            "</if>" +
                    "<if test='cities != null and cities.length>0'>" +
                    "<foreach collection='cities' item='id' open='and (' separator=' or ' close=')'>",
            "  JSON_CONTAINS(over_area->'$[*][1]','\"${id}\"')",
            "</foreach>",
            "</if>" +
                    " order BY crm_partner.update_time  DESC" +

                    "</script>"
    })
    List<Partner> findAssetInf(String createId, String company, Integer partnerType, String belong, @Param("cities") String[] cities, String orgType, String orgRemark, String typeId);


    //根据 asset_info信息查询
    @ResultMap(value = "partnerMap")
    @Select({
            "<script>",
            "select crm_partner.*,sys_user.true_name as trueName from crm_partner left join sys_user on crm_partner.create_user_id =sys_user.user_id" +
                    " where   crm_partner.partner_type = #{partnerType}  " +
                    "<if test='company != null and company!=\"\"'>" +
                    " and company like '%${company}%' ",
            "</if>" +
                    "<if test='createId != null and createId!=\"\"'>" +
                    " and crm_partner.create_user_id = #{createId}",
            "</if>" +
                    "<if test='typeId != null and typeId!=\"\"'>" +
                    " and json_contains(asset_info->'$.businessType[*]','\"${typeId}\"')",
            "</if>" +
                    "<if test='belong != null and belong!=\"\"'>" +
                    " and asset_info->'$.belong'=#{belong}",
            "</if>",
            "<if test='orgType != null and orgType!=\"\"'>" +
                    "  and  JSON_CONTAINS(org_type->'$[*]' , '\"${orgType}\"')",
            "</if>" +
                    "<if test='orgRemark != null and orgRemark!=\"\"'>" +
                    " and crm_partner.org_remark = #{orgRemark}",
            "</if>" +
                    "<if test='depId != null and depId!=\"\"'>" +
                    " and sys_user.dep_id = #{depId}",
            "</if>" +
                    "<if test='cities != null and cities.length>0'>" +
                    "<foreach collection='cities' item='id' open='and (' separator=' or ' close=')'>",
            "  JSON_CONTAINS(over_area->'$[*][1]','\"${id}\"')",
            "</foreach>",
            "</if>" +
                    " order BY crm_partner.update_time  DESC" +

                    "</script>"
    })
    List<Partner> findAssetInfSys(String depId, String createId, String company, Integer partnerType, String belong, @Param("cities") String[] cities, String orgType, String orgRemark, String typeId);


    //根据 fund_info信息查   询
    @ResultMap(value = "partnerMap")
    @Select({
            "<script>",
            "select crm_partner.*,sys_user.true_name as trueName from crm_partner left join sys_user on crm_partner.create_user_id =sys_user.user_id" +
                    " where    crm_partner.partner_type = #{partnerType} " +
                    "<if test='depId != null and depId!=\"\"'>" +
                    " and sys_user.dep_id = #{depId}",
            "</if>" +
                    "<if test='createId != null and createId!=\"\"'>" +
                    " and crm_partner.create_user_id = #{createId}",
            "</if>" +
                    "<if test='typeId != null and typeId!=\"\"'>" +
                    " and json_contains(fund_info->'$.fancyList[*].typeId','\"${typeId}\"')" +
                    "</if>" +
                    "<if test='company != null and company!=\"\"'>" +
                    " and company like '%${company}%' " +
                    "</if>" +

                    "<if test='typeName != null and typeName!=\"\"'>",
            " and json_contains(fund_info->'$.fancyList[*].typeName','\"${typeName}\"')",
            "</if>",
            "<if test='orgType != null and orgType!=\"\"'>",
            " and  JSON_CONTAINS(org_type->'$[*]' , '\"${orgType}\"')",
            "</if>",
            "<if test='orgRemark != null and orgRemark!=\"\"'>",
            " and crm_partner.org_remark = #{orgRemark}",
            "</if>",
            "<if test='currency != null and currency!=\"\"'>",
            " and json_contains(fund_info->'$.structList[*].currency','\"${currency}\"')",
            "</if>",
            "<if test='incomeType != null and incomeType!=\"\"'>",
            " and json_contains(fund_info->'$.structList[*].incomeType','\"${incomeType}\"')",
            "</if>",
            "<if test='struct != null and struct!=\"\"'>",
            " and json_contains(fund_info->'$.structList[*].struct','\"${struct}\"')",
            "</if>",
            "<if test='scaleMin != null '>",
            " and cast(SUBSTRING(fund_info->'$.structList[*].scaleMin',4,1 ) as UNSIGNED INTEGER) &gt;= #{scaleMin}",
            "</if>",
            "<if test='scaleMax != null '>",
            " and cast(SUBSTRING(fund_info->'$.structList[*].scaleMax',4,1 ) as UNSIGNED INTEGER) &lt;= #{scaleMax}",
            "</if>",
            "<if test='deadline != null and deadline!=\"\"'>",
            " and json_contains(fund_info->'$.structList[*].deadline','\"${deadline}\"')",
            "</if>" +
                    "<if test='cities != null and cities.length>0'>" +
                    "<foreach collection='cities' item='id' open='and (' separator=' or ' close=')'>",
            "  JSON_CONTAINS(over_area->'$[*][1]','\"${id}\"')",
            "</foreach>" +
                    "</if>" +
                    "<if test='incomeRate != null '>" +
                    " and json_contains(fund_info->'$.structList[*].incomeRate', '${incomeRate}')",
            "</if>" +
                    " order BY crm_partner.update_time  DESC",
            "</script>",
    })
    List<Partner> findFundInfoSys(String depId, String createId, String company, Integer partnerType, String orgType, String orgRemark,
                                  String currency, String incomeType,
                                  String struct, Integer scaleMin, Integer scaleMax, String deadline,
                                  @Param("cities") String[] cities, String typeId, String typeName, Integer incomeRate);


    //根据 fund_info信息查询
    @ResultMap(value = "partnerMap")
    @Select({
            "<script>",
            "select crm_partner.*,sys_user.true_name as trueName from crm_partner left join sys_user on crm_partner.create_user_id =sys_user.user_id" +
                    " where   crm_partner.disabled=0 and crm_partner.partner_type = #{partnerType} " +
                    "<if test='typeId != null and typeId!=\"\"'>" +
                    " and json_contains(fund_info->'$.fancyList[*].typeId','\"${typeId}\"')" +
                    "</if>" +
                    "<if test='company != null and company!=\"\"'>" +
                    " and company like '%${company}%' " +
                    "</if>" +
                    "<if test='createId != null and createId!=\"\"'>" +
                    " and crm_partner.create_user_id = #{createId}",
            "</if>" +
                    "<if test='typeName != null and typeName!=\"\"'>",
            " and json_contains(fund_info->'$.fancyList[*].typeName','\"${typeName}\"')",
            "</if>",
            "<if test='orgType != null and orgType!=\"\"'>",
            " and  JSON_CONTAINS(org_type->'$[*]' , '\"${orgType}\"')",
            "</if>",
            "<if test='orgRemark != null and orgRemark!=\"\"'>",
            " and crm_partner.org_remark = #{orgRemark}",
            "</if>",
            "<if test='currency != null and currency!=\"\"'>",
            " and json_contains(fund_info->'$.structList[*].currency','\"${currency}\"')",
            "</if>",
            "<if test='incomeType != null and incomeType!=\"\"'>",
            " and json_contains(fund_info->'$.structList[*].incomeType','\"${incomeType}\"')",
            "</if>",
            "<if test='struct != null and struct!=\"\"'>",
            " and json_contains(fund_info->'$.structList[*].struct','\"${struct}\"')",
            "</if>",
            "<if test='scaleMin != null '>",
            " and cast(SUBSTRING(fund_info->'$.structList[*].scaleMin',4,1 ) as UNSIGNED INTEGER) &gt;= #{scaleMin}",
            "</if>",
            "<if test='scaleMax != null '>",
            " and cast(SUBSTRING(fund_info->'$.structList[*].scaleMax',4,1 ) as UNSIGNED INTEGER) &lt;= #{scaleMax}",
            "</if>",
            "<if test='deadline != null and deadline!=\"\"'>",
            " and json_contains(fund_info->'$.structList[*].deadline','\"${deadline}\"')",
            "</if>" +
                    "<if test='cities != null and cities.length>0'>" +
                    "<foreach collection='cities' item='id' open='and (' separator=' or ' close=')'>",
            "  JSON_CONTAINS(over_area->'$[*][1]','\"${id}\"')",
            "</foreach>" +
                    "</if>" +
                    "<if test='incomeRate != null '>" +
                    " and json_contains(fund_info->'$.structList[*].incomeRate', '${incomeRate}')",
            "</if>" +
                    " order BY crm_partner.update_time  DESC",
            "</script>",
    })
    List<Partner> findFundInfo(String createId, String company, Integer partnerType, String orgType, String orgRemark,
                               String currency, String incomeType,
                               String struct, Integer scaleMin, Integer scaleMax, String deadline,
                               @Param("cities") String[] cities, String typeId, String typeName, Integer incomeRate);

    @Select("SELECT crm_menu.menu_path FROM crm_menu inner join crm_role_menu on crm_menu.menu_id =crm_role_menu.menu_id" +
            " where crm_role_menu.role_id =#{roleId}")
    List<String> path(String roleId);

    @Select("select dep_id from sys_user where user_id=#{userId} and disabled=0")
    String depId(String userId);


    @Update("UPDATE crm_partner SET disabled=0 WHERE partner_id=#{partnerId} ")
    int reduction(String partnerId);


    /**
     * 查询 相同信息的伙伴id
     *
     * @return
     */
    @Select(
            "select a.partner_id from crm_partner a inner join\n" +
                    "(select * from crm_partner where\n" +
                    "partner_id=#{partnerId}) b on a.name=b.name and\n" +
                    "a.telephone=b.telephone and a.weixin=b.weixin and a.email=b.email\n" +
                    "and a.telephone_list=b.telephone_list"
    )
    List<String> partnerId(String partnerId);

    @Update("<script>" +
            "update crm_partner set name=#{name},sex=#{sex},telephone=#{telephone}," +
            "weixin=#{weixin},email=#{email},telephone_list=#{telephoneList,typeHandler=com.crm.typehandler.JsonTypeHandler}" +
            " where partner_id in " +
            "<foreach collection='part' item='id' open='(' separator=' , ' close=')'>" +
            " #{id} " +
            "</foreach>" +
            "</script>")
    int updateAll(String name, String sex, String telephone, String weixin,
                  String email, List<Telephone> telephoneList, @Param("part") List<String> part);
}
